package com.bdqn.BookManage.util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author : CP
 * @version: 1.0
 * @program : PetShop
 * @description : 工具类
 * @date : 2020-07-08 11:13
 **/
public class DBUtil {

    private static String url = "jdbc:mysql:///BookManage?characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "";

    /**
     * @author : CP
     * @version: 1.0
     * @program : PetShop
     * @description : 获取数据库连接
     * @date : 2020-07-08 11:13
     * @return Connection
     */
    public static Connection open(){
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @author : CP
     * @version: 1.0
     * @program : PetShop
     * @description : 关闭数据库连接
     * @date : 2020-07-08 11:13
     * @param conn 数据库连接
     * @param ps 获取SQL语句执行对象
     * @param rs 结果集
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){

        if(rs != null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if(ps != null){
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     /**
     * @author : CP
     * @version: 1.0
     * @program : PetShop
     * @description : 公共的增删改
     * @date : 2020-07-08 11:13
     * @param sql SQL语句
     * @param param 参数列表
     * @return boolean
     * @throws SQLException 抛异常，数据修改问题
     */
    public static boolean connZsg(String sql,Object [] param) {
        /** 打开数据库连接 */
        Connection conn = open();
        /** 获取SQL语句执行对象 */
        PreparedStatement ps = null;
        int sum = 0;

        try {
            ps = conn.prepareStatement(sql);
            if(param != null){
                for (int i = 0; i < param.length; i++) {
                    ps.setObject((i+1),param[i]);
                }
            }
            sum = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn,ps,null);
        }

        return sum > 0;
    }

    /**
     * @author : CP
     * @version: 1.0
     * @program : PetShop
     * @description : 公共的查询所有
     * @date : 2020-07-08 11:13
     * @param sql SQL语句
     * @param param 属性字段
     * @param cla 类
     * @return List<?>
     */
    public static List<?> queryAll(String sql,Object [] param,Class cla){

        Connection conn = open();
        PreparedStatement ps = null;
        ResultSet rs = null;

        /** 创建集合 */
        List<Object> list = new ArrayList<>();

        /** 处理SQL对象执行对象 */
        try {
            ps = conn.prepareStatement(sql);
            if(param != null){
                for (int i = 0; i < param.length; i++) {
                    ps.setObject((i+1),param[i]);
                }
            }
            rs = ps.executeQuery();

            while(rs.next()){
                /** 实例化未知的class对象 */
                Object obj = cla.newInstance();

                /** 获取对象的所有字段 */
                Field[] fields = cla.getDeclaredFields();

                for (Field field:fields) {
                    /** 获取属性权限 */
                    field.setAccessible(true);
                    /** 为属性赋值 */
                    field.set(obj,rs.getObject(field.getName()));
                }
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(conn,ps,rs);
        }

        return list;
    }
}
